package com.nccsjz.back.bulletin.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.nccsjz.base.BaseDAO;
import com.nccsjz.pojo.Bulletin;
import com.nccsjz.utils.DateUtil;
import com.nccsjz.utils.Pager;
public class BulletinDAO extends BaseDAO{
    private boolean flag;
	public BulletinDAO(Connection con) {
		super(con);
	}
    //add bulletin
	public boolean addBullet(Bulletin bulletin) throws SQLException{
		try{
		String sql="insert into bulletin (bulletintitle,bulletincontent,bulletindate) values(?,?,?)";
        pstmt=con.prepareStatement(sql);
        pstmt.setString(1, bulletin.getBulletintitle());
        pstmt.setString(2, bulletin.getBulletincontent());
        if(null!=bulletin.getBulletindate()){
        	 pstmt.setDate(3, DateUtil.convertDate(bulletin.getBulletindate()));
        }else{
        	pstmt.setDate(3, DateUtil.convertDate(new java.util.Date()));
        }
        int num=pstmt.executeUpdate();
        if(num!=0){
        	flag=true;
        }
		
	   }catch(Exception ex){
			ex.printStackTrace();
		}finally{
		   closeAll(rs, pstmt);
		}
		return flag;
	}
	
	//eidt bulletin
	public boolean updateBulletin(Bulletin bulletin) throws SQLException {
		try{
			String sql="update bulletin set bulletintitle=?,bulletincontent=? where bulletinid=?";
		    pstmt=con.prepareStatement(sql);
		    pstmt.setString(1, bulletin.getBulletintitle());
		    pstmt.setString(2, bulletin.getBulletincontent());
		    pstmt.setLong(3, bulletin.getBulletinid());
		    int num=pstmt.executeUpdate();
		    if(num!=0){
		    	flag=true;
		    }
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeAll(rs, pstmt);
		}
		return flag;
	}
	//get one Bulletin
	public Bulletin getBulletin(Long id) throws SQLException{
		Bulletin bulletin=null;
		try{
			String sql="select * from bulletin where bulletinid=?";
			pstmt=con.prepareStatement(sql);
			pstmt.setLong(1, id);
			rs=pstmt.executeQuery();
			if(rs.next()){
				bulletin=new Bulletin();
				bulletin.setBulletinid(rs.getLong("bulletinid"));
				bulletin.setBulletintitle(rs.getString("bulletintitle"));
				bulletin.setBulletincontent(rs.getString("bulletincontent"));
				bulletin.setBulletindate(rs.getDate("bulletindate"));
			}
		}catch(Exception ex){
			ex.printStackTrace();
			
		}finally{
			closeAll(rs, pstmt);
		}
		return bulletin;
	}
	//delete bulletin
	public boolean deleteBulletin(Long id) throws SQLException{
		try{
			String sql="delete from bulletin where bulletinid=?";
    		pstmt=con.prepareStatement(sql);
    		pstmt.setLong(1, id);
    		int num=pstmt.executeUpdate();
    		if(num!=0){
    			flag=true;
    		}
    	}catch(Exception ex){
    		ex.printStackTrace();
    	}finally{
    		closeAll(rs,pstmt);
    	}
    	return flag;
	}
	//batch deletebulletin
	public boolean deleteBathBulletin(List<Long> list)  throws SQLException{
		 int num[]=null;
		 try{
			String sql="delete from bulletin where bulletinid=?";
			pstmt=con.prepareStatement(sql);
			for(int i=0;i<list.size();i++){
				 pstmt.setLong(1,list.get(i));
				 pstmt.addBatch();
			}
			num=pstmt.executeBatch();
			if(num!=null){
				flag=true;
			}
			 
		 }catch(Exception ex){
			 ex.printStackTrace();
		 }finally{
			 closeAll(rs,pstmt);
			 
		 }
		 return flag;
	 }
	//pager display bulletin
	 public Pager getBulletin(int pageSize,int pageNo) throws SQLException{
    	 List<Bulletin> list=new ArrayList<Bulletin>();
    	 int rownum=0;
    	 try{
    	 String sql1="select count(*) from bulletin";
    	 //String sql2="select * from(select u.*,rownum rn from(select * from bulletin order by bulletindate desc) u where rownum<=?)where rn>?";
    	 String sql2 = "select * from (select bulletin.*,row_number() over(order by bulletinid desc) rn " 
    		 		+ " from bulletin) u where u.rn > ? and u.rn <= ?";
    	 pstmt=con.prepareStatement(sql1);
    	 rs=pstmt.executeQuery();
    	 if(rs.next()){
    		 rownum=rs.getInt(1);
    	 }
    	 pstmt=con.prepareStatement(sql2);
    	 pstmt.setInt(1, (pageNo-1)*pageSize);
    	 pstmt.setInt(2, pageNo*pageSize);    	 
    	 rs=pstmt.executeQuery();
    	 while(rs.next()){
    		 Bulletin bulletin=new Bulletin();
    		 bulletin.setBulletinid(rs.getLong("bulletinid"));
    		 bulletin.setBulletintitle(rs.getString("bulletintitle"));
    		 bulletin.setBulletincontent(rs.getString("bulletincontent"));
    		 bulletin.setBulletindate(rs.getDate("bulletindate"));
    		 list.add(bulletin);
    	 }
    	 }catch(Exception ex){
    		 ex.printStackTrace();
    	 }finally{
    		 closeAll(rs,pstmt);
    	 }
    	 Pager pager=new Pager(pageSize,pageNo,rownum,list);
    	 return pager;
     }

		/**
		 * 最新 公告.
		 * 即在表中ID最大的一条记录。 add by cpf 
		 * @return
		 */
		public Bulletin getLastBulletin() throws Exception {
			Bulletin bulletin = null;
			
			try {
				
				String sql = "select * from bulletin b where b.bulletinid=(select max(bulletinid) from bulletin)";
				pstmt = con.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if (rs.next()) {
					bulletin = new Bulletin();
					bulletin.setBulletinid(rs.getLong("bulletinid"));
					bulletin.setBulletintitle(rs.getString("bulletintitle"));
					bulletin.setBulletincontent(rs.getString("bulletincontent"));
					bulletin.setBulletindate(rs.getDate("bulletindate"));
				}
			} catch (Exception ex) {
				ex.printStackTrace();

			} finally {
				closeAll(rs, pstmt);
			}
			return bulletin;
		}
}
